package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Utils.StringUtil;

public class MysqlConnect {
	private static final String servidor = "localhost";
	private static final String usuario = "root";
	private static final String senha = "";
	private static final String banco = "sysevents";
	private Connection connection;

	public Connection connect() {
		String url = "jdbc:mysql://" + servidor + ":3306/" + banco;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return connection = DriverManager
					.getConnection(url, usuario, senha);
		} catch (ClassNotFoundException e) {
			System.out.println("Driver não encontrado");
			return null;
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return null;
		}
	}

	public void desconnect() {
		try {
			if (getConnection() != null) {
				getConnection().close();
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	public String getServidor() {
		return servidor;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public ArrayList consultar(String sql) {
		connect();
		ArrayList dados = new ArrayList<>();
		try {
			Statement st = getConnection().createStatement();
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				Object item[] = new Object[result.getMetaData()
						.getColumnCount()];
				for (int i = 1; i <= result.getMetaData().getColumnCount(); i++) {
					item[i - 1] = result.getObject(i);
				}
				dados.add(item);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			desconnect();
		}
		return dados;
	}
	public ResultSet executeQueryResult(String sql){
		connect();
		ResultSet result = null;
		try {
			Statement st = getConnection().createStatement();
			result = st.executeQuery(sql);
		} catch (SQLException e) {
			System.out.println("Query error");
			System.out.println(e.getMessage());
		}
		return result;
	}
	public boolean executeUpdate(String sql){
		 connect();
	        boolean conectou = true;
	        try{
	          if(getConnection() != null){
	            Statement st = getConnection().createStatement();
	            st.execute(sql);
	          }
	        }catch(Exception ex){
	           System.out.println("Erro no comando:"+sql);
	           System.out.println(ex.getMessage());   
	           conectou = false;
	        }finally{       
	          desconnect();
	        }
	        return conectou;
		
	}
	public boolean update(String table, String cols, String where){
		StringBuilder str = new StringBuilder();
		str.append("UPDATE ");
		str.append(table);
		str.append(" SET ");
	    str.append(cols);
	    if (where.length() < 0) {
	    	return false;
	    }
	    
    	str.append(" WHERE(");
    	str.append(where);
    	str.append(") ");
	    
    	return executeUpdate(str.toString());
	}
	public ResultSet select(String table , String cols , String where , String limit , String order){
	    StringBuilder strBuf = new StringBuilder() ;
	    strBuf.append("SELECT ");
		
	    if (cols.length() == 0) {
	    	strBuf.append("*");
	    }
	    else {
	    	strBuf.append(cols);
	    }
	    
	    strBuf.append(" FROM ");
	    strBuf.append(table);
	    
	    if (where.length() > 0) {
	    	strBuf.append(" WHERE(");
	    	strBuf.append(where);
	    	strBuf.append(") ");
	    }
	    
	    if (order.length() > 0) {
	    	strBuf.append(" ORDER BY ");
	    	strBuf.append(order);
	    }
	    
	    if (limit.length() > 0) {
	    	strBuf.append(" LIMIT ");
	    	strBuf.append(limit);
	    }
	    
	    System.out.println(strBuf.toString());
	    ResultSet rs = executeQueryResult(strBuf.toString());
	    
	    if( rs == null ) return null;
	    
	    return rs;
	}
	public ResultSet join(String table, String table2, String cols , String where, String groupby, String order){
	    StringBuilder strBuf = new StringBuilder() ;
	    strBuf.append("SELECT ");
		
	    if (cols.length() == 0) {
	    	strBuf.append("*");
	    }
	    else {
	    	strBuf.append(cols);
	    }
	    
	    strBuf.append(" FROM ");
	    strBuf.append(table);
	    
	    strBuf.append(" join ");
	    strBuf.append(table2);
	    if (where.length() > 0) {
	    	strBuf.append(" on ");
	    	strBuf.append(where);
	    	strBuf.append(" ");
	    }
	    if (groupby.length() > 0) {
	    	strBuf.append(" group by " + groupby);
	    }
	    strBuf.append(" order by " + order);
	    System.out.println(strBuf.toString());
	    ResultSet rs = executeQueryResult(strBuf.toString());
	    
	    if( rs == null ) return null;
	    
	    return rs;
	}

}
